﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.IO;
using System.Data;
using System.Data.OleDb;

namespace SAPBoAddonUI
{
    /// <summary>
    /// Utilities class that supply some static method such as format xml document to a humanly-readable format
    /// </summary>
    /// <author>Webb.Wen</author>
    /// <lastedit>2011-07-14 13:21:15</lastedit>
    public class Utilities
    {
        /// <summary>
        /// 格式化Xml
        /// </summary>
        /// <param name="inputXml">xml字符串</param>
        /// <returns></returns>
        public static string FormatXml(string inputXml)
        {
            XmlDocument document = new XmlDocument();
            document.Load(new StringReader(inputXml));
            StringBuilder builder = new StringBuilder();
            using (XmlTextWriter writer = new XmlTextWriter(new StringWriter(builder)))
            {
                writer.Formatting = Formatting.Indented;
                document.Save(writer);
            }
            return builder.ToString();
        }
        /// <summary>
        /// 读取excel到DataTable（翻译自Transform）
        /// </summary>
        /// <param name="strExcelFileName">excel文件路径</param>
        /// <param name="strSheetName">sheet名称</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string fileName, string SheetName)
        {
            string connectionString = GetConnectionString(fileName);
            string sql_Select = "select * from [{0}]";
            System.Data.DataTable table = new System.Data.DataTable();
            if (System.IO.File.Exists(fileName))
            {
                using (System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(string.Format(connectionString, fileName)))
                {
                    Conn.Open();

                    OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format(sql_Select, SheetName), Conn);
                    adapter.Fill(table);
                    table.TableName = SheetName;
                }
            }
            return table;
        }
        public static List<string> GetSheetNames(string fileName)
        {
            string connectionString = GetConnectionString(fileName);

            System.Data.DataTable table = new System.Data.DataTable();
            List<string> ret = new List<string>();
            if (System.IO.File.Exists(fileName))
            {
                using (System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(string.Format(connectionString, fileName)))
                {
                    Conn.Open();
                    table = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    foreach (DataRow item in table.Rows)
                    {
                        ret.Add(item["TABLE_NAME"].ToString());
                    }
                }
            }
            return ret;
        }
        private static string GetConnectionString(string fileName)
        {
            string connectionString;
            if (fileName.EndsWith("xls", StringComparison.InvariantCultureIgnoreCase))
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= {0};Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
            }
            else
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= {0};Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1'";
            }
            return connectionString;
        }
    }
}
